/**
 * 配送单相关功能
 */
import { mysql } from 'src-ssr/db/connect';
import TableHelper from 'src-ssr/db/tableHelper';
import * as tools from 'src-ssr/util/common';
import logs from 'src-ssr/util/logs';
import { Sequelize, Op, QueryTypes, where } from 'sequelize';
import models, { sequelize } from 'src-ssr/db/sequelize';
import moment from 'moment';
import autocode from 'src-ssr/util/autocode';
import shortid from 'shortid';
import { URL } from 'node:url';
import workflow from 'src-ssr/util/workflow';
import * as exportFile from 'src-ssr/util/exportToFile';
import path from 'path';
import fs from 'fs';

import * as singleUtils from 'src-ssr/controller/common/singleOperate';
import * as mdUtils from 'src-ssr/controller/common/masterDetailOperate';

import listConfig from 'app/search/tenant/delivery/list.js';
import summaryConfig from 'app/search/tenant/delivery/summary.js';
import taskConfig from 'app/search/tenant/delivery/task.js';

const menuName = 'delivery';
const modelName = 't_deliveries';
const modelLabel = '配送单';
const detailModelName = 't_delivery_details'; // 从表名
const associatedFieldName = 'master_uid'; // 从表与主表的关联字段
const keyFieldName = 'uid';

// 取数据
async function fetchData ({ startRow, count, filter, sortBy, descending, user }) {
  const detailWhere = {};
  const where = await tools.assemblyWhere({
    filter,
    searchConfig: listConfig.search,
    fn: ({ where, field, search }) => {
      if (field === 'sale_bill_no') {
        if (!detailWhere.sale_detail_uid) detailWhere.sale_detail_uid = { [Op.or]: [] };

        detailWhere.sale_detail_uid[Op.or].push({
          [Op.in]: Sequelize.literal(`(
            SELECT d.uid
            FROM t_sale_details d
            LEFT JOIN t_sales m ON d.master_uid=m.uid
            WHERE d.tenant_id = ${mysql.escape(user.tid)}
            AND m.bill_no LIKE ${mysql.escape('%' + search.trim().replace(/%/g, '') + '%')}
          )`)
        });
      } else if (field === 'type') {
        if (!detailWhere.sale_detail_uid) detailWhere.sale_detail_uid = { [Op.or]: [] };

        detailWhere.sale_detail_uid[Op.or].push({
          [Op.in]: Sequelize.literal(`(
            SELECT d.uid
            FROM t_sale_details d
            LEFT JOIN t_sales m ON d.master_uid=m.uid
            WHERE d.tenant_id = ${mysql.escape(user.tid)}
            AND m.type = ${mysql.escape(search)}
          )`)
        });
      } else if (field === 'customer_uid') {
        if (!detailWhere.sale_detail_uid) detailWhere.sale_detail_uid = { [Op.or]: [] };

        detailWhere.sale_detail_uid[Op.or].push({
          [Op.in]: Sequelize.literal(`(
            SELECT d.uid
            FROM t_sale_details d
            LEFT JOIN t_sales m ON d.master_uid=m.uid
            WHERE d.tenant_id = ${mysql.escape(user.tid)}
            AND m.customer_uid = ${mysql.escape(tools.uuidToBin(search))}
          )`)
        });
      } else if (field === 'consumer_name') {
        if (!detailWhere.sale_detail_uid) detailWhere.sale_detail_uid = { [Op.or]: [] };

        detailWhere.sale_detail_uid[Op.or].push({
          [Op.in]: Sequelize.literal(`(
            SELECT d.uid
            FROM t_sale_details d
            LEFT JOIN t_sales m ON d.master_uid=m.uid
            LEFT JOIN m_consumers c ON m.consumer_uid=c.uid
            WHERE d.tenant_id = ${mysql.escape(user.tid)}
            AND c.nickname LIKE ${mysql.escape('%' + search.trim().replace(/%/g, '') + '%')}
          )`)
        });
      } else if (field === 'material_uid') {
        if (!detailWhere.sale_detail_uid) detailWhere.sale_detail_uid = { [Op.or]: [] };

        detailWhere.sale_detail_uid[Op.or].push({
          [Op.in]: Sequelize.literal(`(
            SELECT d.uid
            FROM t_sale_details d
            WHERE d.tenant_id = ${mysql.escape(user.tid)}
            AND d.material_uid = ${mysql.escape(tools.uuidToBin(search))}
          )`)
        });
      }
    }
  });
  where.tenant_id = user.tid; // 只取该租户的数据

  const exclude = ['uid', 'history'];
  // 只取单条数据
  if (filter && filter.id) {
    where.id = filter.id;
    exclude.splice(exclude.indexOf('history'), 1);
  }

  const order = sortBy ? [[sortBy, descending ? 'DESC' : 'ASC']] : [];

  let right = { data: true };
  if (user) {
    right = await tools.checkUserRight(user, menuName, ['data']);
    if (!right.data) { // 是否有查看全部的权限
      where.create_user_id = user.id;
    }
  }

  let ret = { rows: [], count: 0 };
  try {
    ret = await models[modelName].findAndCountAll({
      attributes: {
        exclude
      },
      include: [{
        model: models[detailModelName],
        attributes: ['id', [Sequelize.literal('bin_to_uuid(t_delivery_details.sale_detail_uid, 1)'), 'sale_detail_uid'], 'deliver_amount', 'remark'],
        include: [{
          model: models.t_sale_details,
          attributes: [['amount', 'sale_amount'], ['remark', 'sale_detail_remark']],
          include: [{
            model: models.t_materials,
            attributes: ['material_name', 'material_model', 'material_name_model', 'material_unit', ['default_image', 'material_image']]
          }, {
            model: models.t_sales,
            attributes: [['bill_no', 'sale_bill_no'], 'type', 'consignee', 'tel', 'address'],
            include: [{
              model: models.t_customers,
              attributes: [['nickname', 'customer_name']]
            }, {
              model: models.m_consumers,
              attributes: [['nickname', 'consumer_name']]
            }]
          }]
        }],
        required: Object.keys(detailWhere).length > 0,
        where: detailWhere
      }],
      where,
      order,
      offset: count ? startRow : undefined,
      limit: count || undefined,
      group: count ? `${modelName}.id` : undefined
    });
  } catch (e) {
    console.error(modelLabel + '列表出错', e);
  }

  // 处理其它数据
  const rows = [];
  for (const row of ret.rows) {
    const jsonRow = row.toJSON();

    if (jsonRow[detailModelName]) {
      // 把明细里的信息拉平
      for (const detail of jsonRow[detailModelName]) {
        tools.flatDetail(['t_sale_detail', 't_material', 't_sale', 't_customer', 'm_consumer'], detail);

        // 数量转数值
        for (const key of ['sale_amount', 'deliver_amount']) {
          if (detail[key]) detail[key] = Number(detail[key]);
        }
      }
    }
    // 转数值
    for (const key of ['delivery_fee']) {
      if (jsonRow[key]) jsonRow[key] = Number(jsonRow[key]);
    }

    if (user) jsonRow._owner = row.create_user_id == user.id; // 是否为拥有者
    rows.push(jsonRow);
  }

  if (filter && filter.id) {
    if (rows.length > 0) {
      return rows[0];
    } else {
      return {};
    }
  } else {
    return {
      data: rows,
      rowsNumber: typeof ret.count === 'object' ? ret.count.length : ret.count
    };
  }
}

// 列表
export const list = async (req, res) => {
  const { startRow, count, filter, sortBy, descending } = req.body;
  const user = await tools.getLoginUser(req.get('Authorization').split(' ')[1]);

  const ret = await fetchData({ startRow, count, filter, sortBy, descending, user });
  res.json(ret);
};

// 详细信息
export const detail = async (req, res) => {
  const { id } = req.body;

  const user = await tools.getLoginUser(req.get('Authorization').split(' ')[1]);
  const ret = await fetchData({ filter: { id }, user });

  res.json(ret);
};

/**
 * 新增
 */
export const create = async (req, res) => {
  const user = await tools.getLoginUser(req.get('Authorization').split(' ')[1]);
  const { formData } = req.body;
  // 生成编号
  if (!formData.bill_no) {
    formData.bill_no = await autocode.newCode({ tid: user.tid, code: 'DL' }); // 生成编号
  }

  // 转换sale_detail_uid
  if (formData[detailModelName] && formData[detailModelName].length > 0) {
    // 明细转json
    if (typeof formData[detailModelName] === 'string') {
      formData[detailModelName] = JSON.parse(formData[detailModelName]);
    }
  }
  formData[detailModelName].forEach(row => {
    console.log(row.sale_detail_uid)
    row.sale_detail_uid = tools.uuidToBin(row.sale_detail_uid);
    console.log(row.sale_detail_uid)
  });

  const ret = await mdUtils.createData({ modelName, modelLabel, menuName, detailModelName, associatedFieldName, keyFieldName }, req, true);

  res.json(ret);
};

/**
 * 更新
 */
export const update = async (req, res) => {
  const { formData } = req.body;

  // 转换sale_detail_uid
  if (formData[detailModelName] && formData[detailModelName].length > 0) {
    // 明细转json
    if (typeof formData[detailModelName] === 'string') {
      formData[detailModelName] = JSON.parse(formData[detailModelName]);
    }
  }

  formData[detailModelName].forEach(row => {
    row.sale_detail_uid = tools.uuidToBin(row.sale_detail_uid);
  });

  const ret = await mdUtils.updateData({ modelName, modelLabel, menuName, detailModelName, associatedFieldName, keyFieldName }, req, true);

  res.json(ret);
};

/**
 * 删除
 */
export const destroy = async (req, res) => {
  const user = await tools.getLoginUser(req.get('Authorization').split(' ')[1]);
  // const snapshot = await fetchData({ filter: { id: req.body.id }, user });
  const ret = await mdUtils.destroyData({ modelName, modelLabel, menuName, detailModelName, associatedFieldName, keyFieldName }, req, true);

  res.json(ret);
};

// 统计
export const summary = async (req, res) => {
  const { startRow, count, filter, sortBy, descending, columns } = req.body;

  const where = await tools.assemblyWhere({
    filter,
    searchConfig: summaryConfig.search,
    raw: true,
    fn: ({ where, field, search }) => {
      if (field === 'status') {
        let subWhere = '';
        if (search === 'not') {
          subWhere = 'ISNULL((SELECT SUM(deliver_amount) FROM t_delivery_details v WHERE v.delivery_detail_uid=d.uid))'
        } else if (search === 'part') {
          subWhere = 'd.amount > (SELECT SUM(deliver_amount) FROM t_delivery_details v WHERE v.delivery_detail_uid=d.uid)';
        } else {
          subWhere = 'd.amount <= (SELECT SUM(deliver_amount) FROM t_delivery_details v WHERE v.delivery_detail_uid=d.uid)';
        }
        where.push(subWhere);
      }
    }
  });

  const user = await tools.getLoginUser(req.get('Authorization').split(' ')[1]);
  const right = await tools.checkUserRight(user, menuName, ['data']);
  if (!right.data) {
    // 是否有查看全部的权限
    where.push(`m.create_user_id=${user.id}`);
  }

  const order = [];
  if (['year', 'month', 'day'].includes(sortBy)) {
    // 日期型的一起排序
    if (columns.includes('year')) {
      order.push(`year ${descending ? 'DESC' : 'ASC'}`);
    }
    if (columns.includes('month')) {
      order.push(`month ${descending ? 'DESC' : 'ASC'}`);
    }
    if (columns.includes('day')) {
      order.push(`day ${descending ? 'DESC' : 'ASC'}`);
    }
  } else {
    sortBy && columns.includes(sortBy) && order.push(`${sortBy} ${descending ? 'DESC' : 'ASC'}`);
  }

  const limit = count ? `LIMIT ${startRow}, ${count}` : '';

  // group by 字段
  const groupField = columns.filter((v) => ['year', 'month', 'day', 'create_user_name',
    'logistics'].includes(v)).join(', ');

  const allDataSql = `SELECT @idx:=@idx+1 AS id, ${groupField ? groupField + ',' : ''}
    COUNT(*) bill_count, SUM(delivery_fee) delivery_fee
    FROM (
      SELECT FROM_UNIXTIME(m.create_time, '%Y') year,
      FROM_UNIXTIME(m.create_time, '%m') month, FROM_UNIXTIME(m.create_time, '%d') day,
      m.create_user_name, m.logistics, m.delivery_fee
      FROM t_deliveries m
      ${where.length > 0 ? ' WHERE ' + where.join(' AND ') : ''}
    ) a
    , (SELECT @idx:=0) p
    GROUP BY ${groupField || ''}`;

  const [stat] = await mysql.query(`SELECT COUNT(*) cnt,
    IFNULL(SUM(bill_count), 0) bill_count,
    IFNULL(SUM(delivery_fee), 0) delivery_fee
    FROM (${allDataSql}) a`);

  let rows = [];
  const sql = `${allDataSql}
    ${order.length > 0 ? ' ORDER BY ' + order.join(',') : ''}
    ${limit}`;
  try {
    [rows] = await mysql.query(sql);
  } catch (e) {
    console.log(e, sql);
  }

  let bill_count = 0, delivery_fee = 0;
  rows.forEach((row) => {
    // 数量转数值
    for (const key of ['bill_count', 'delivery_fee']) {
      if (row[key]) row[key] = Number(row[key]);
    }

    bill_count += row.bill_count;
    delivery_fee += row.delivery_fee;
  });

  res.json({
    data: rows,
    rowsNumber: stat[0].cnt,
    summary: {
      total: {
        bill_count: Number(stat[0].bill_count),
        delivery_fee: Number(stat[0].delivery_fee)
      },
      page: {
        bill_count,
        delivery_fee
      }
    }
  });
};

// 任务
export const task = async (req, res) => {
  const { startRow, count, filter, sortBy, descending } = req.body;

  const where = await tools.assemblyWhere({
    filter,
    searchConfig: taskConfig.search,
    raw: true
  });

  // 根据id数量
  if (filter && filter.ids) {
    where.push(`d.id IN (${mysql.escape(filter.ids)})`);
  }

  const user = await tools.getLoginUser(req.get('Authorization').split(' ')[1]);
  const right = await tools.checkUserRight(user, menuName, ['add']);
  if (!right.add) {
    // 是否有新增的权限
    return res.json({
      type: menuName,
      data: [],
      rowsNumber: 0
    });
  }
  where.push(`m.tenant_id=${user.tid}`);
  where.push('m.status = "normal"');

  const orderby = sortBy ? `ORDER BY ${sortBy} ${descending ? 'DESC' : 'ASC'}` : '';
  const limit = count ? `LIMIT ${startRow}, ${count}` : '';

  let data = null;
  let rowsNumber = 0;
  try {
    const tableSql = `FROM t_sales m
      LEFT JOIN t_sale_details d ON m.uid=d.master_uid
      LEFT JOIN t_materials t ON d.material_uid=t.uid
      LEFT JOIN t_customers c ON m.customer_uid=c.uid
      LEFT JOIN m_consumers n ON m.consumer_uid=n.uid
      WHERE d.amount>IFNULL((SELECT SUM(tdd.deliver_amount) FROM t_delivery_details tdd WHERE tdd.sale_detail_uid=d.uid), 0)
      ${where.length > 0 ? ' AND (' + where.join(' AND ') + ')' : ''}`;

    // 取总数
    const rowSum = await sequelize.query(
      `SELECT COUNT(*) cnt
      ${tableSql}`,
      { type: sequelize.QueryTypes.SELECT }
    );
    rowsNumber = rowSum[0].cnt;

    data = await sequelize.query(
      `SELECT d.id, m.bill_no, m.type, m.consignee, m.tel, m.address, bin_to_uuid(d.uid, 1) uid,
      c.nickname customer_name, n.nickname consumer_name, m.consignee, m.tel, m.address,
      t.material_name_model, t.material_unit, t.default_image material_image, d.amount sale_amount,
      IFNULL((SELECT SUM(tdd.deliver_amount) FROM t_delivery_details tdd WHERE tdd.sale_detail_uid=d.uid), 0) deliver_amount,
      d.amount - IFNULL((SELECT SUM(tdd.deliver_amount) FROM t_delivery_details tdd WHERE tdd.sale_detail_uid=d.uid), 0) left_amount
      ${tableSql}
      ${orderby || ''}
      ${limit || ''}`,
      { type: sequelize.QueryTypes.SELECT }
    );
  } catch (e) {
    console.log(e);
  }

  res.json({
    type: menuName,
    data,
    rowsNumber
  });
};

// 任务数量
export const taskNumber = async (req, res) => {
  const user = await tools.getLoginUser(req.get('Authorization').split(' ')[1]);
  const right = await tools.checkUserRight(user, menuName, ['add']);

  let taskNumber = 0; // 任务数据

  if (right.add) {
    // 是否有新增任务
    try {
      const data = await sequelize.query(
        `SELECT COUNT(*) cnt
        FROM t_sale_details d
        LEFT JOIN t_sales m ON d.master_uid=m.uid
        WHERE d.amount>IFNULL((SELECT SUM(tdd.deliver_amount) FROM t_delivery_details tdd WHERE tdd.sale_detail_uid=d.uid), 0)
        AND d.tenant_id=${user.tid} AND m.status = 'normal'`,
        { type: sequelize.QueryTypes.SELECT }
      );
      taskNumber = data[0].cnt;
    } catch (e) {
      console.log(e);
    }
  }

  res.json({
    type: menuName,
    taskNumber
  });
};

// 获取分享链接
export const shareUrl = async (req, res) => {
  const { id } = req.body;
  const row = await models[modelName].findByPk(id);
  if (!row) {
    return res.json({ result: 'fail', message: `不存在该${modelLabel}数据` });
  }
  let suid = row.share_uid;
  if (row.share_uid.length >= 36) {
    suid = shortid.generate();
    await models[modelName].update({ share_uid: suid }, { where: { id } });
  }
  const url = new URL(`share/${menuName}/` + suid, global.domainHost);

  res.json({ result: 'success', url });
};
